Database applications should be restricted from using static DDL statements to modify the application schema.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-3727	DG0015-SQLServer9	SV-24072r2_rule	ECSD-1 ECSD-2	Low

Description
Application users by definition and job function require only the permissions to manipulate data within database objects and execute procedures within the database. The statements used to define objects in the database are referred to as Data Definition Language (DDL) statements and include the CREATE, DROP, and ALTER object statements (DDL statements do not include CREATE USER, DROP USER, or ALTER USER actions). This requirement is included here as a production system would by definition not support changes to the data definitions. Where object creation is an indirect result of DBMS operation or dynamic object structures are required by the application function as is found in some object-oriented DBMS applications, this restriction does not apply. Re-use of static data structures to recreate temporary data objects are not exempted.

Description

Application users by definition and job function require only the permissions to manipulate data within database objects and execute procedures within the database. The statements used to define objects in the database are referred to as Data Definition Language (DDL) statements and include the CREATE, DROP, and ALTER object statements (DDL statements do not include CREATE USER, DROP USER, or ALTER USER actions). This requirement is included here as a production system would by definition not support changes to the data definitions. Where object creation is an indirect result of DBMS operation or dynamic object structures are required by the application function as is found in some object-oriented DBMS applications, this restriction does not apply. Re-use of static data structures to recreate temporary data objects are not exempted.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-23847r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT s.name AS [Schema], o.name AS [Object], u.type_desc AS [TYPE], o.create_date AS [Create Date] FROM sys.schemas s JOIN sys.database_principals u ON s.principal_id = u.principal_id JOIN sys.all_objects o ON s.schema_id = o.schema_id WHERE u.name NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys') AND o.is_ms_shipped = 0 ORDER BY s.name, o.name, o.create_date View the list of objects listed by the queries above. If any object creation dates do not coincide with the software maintenance and upgrade logs or are not objects documented as supporting dynamic object creation functions, investigate the circumstances under which the object was created. If the object is created using static definitions to store temporary data or indicates that the application uses unauthorized DDL statements, this is a Finding. The results of these queries will just give an indication of what objects were created since the database installation or its most recent upgrade. It should not be used as a complete result. For example, using the queries listed above, application objects created with the database installation will not be reported as well as objects owned by default application owner accounts. If the creation dates are more recent than the installation or latest upgrade of the application, the application may be using DDL statements. If the listed objects are not documented in the System Security Plan, this is a Finding.

Check Text ( C-23847r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE name NOT IN ('tempdb', 'ReportServerTempDB')
AND state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT s.name AS [Schema], o.name AS [Object], u.type_desc AS [TYPE], o.create_date AS [Create Date]
FROM sys.schemas s
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE u.name NOT IN ('dbo', 'INFORMATION_SCHEMA', 'sys')
AND o.is_ms_shipped = 0
ORDER BY s.name, o.name, o.create_date

View the list of objects listed by the queries above. If any object creation dates do not coincide with the software maintenance and upgrade logs or are not objects documented as supporting dynamic object creation functions, investigate the circumstances under which the object was created. If the object is created using static definitions to store temporary data or indicates that the application uses unauthorized DDL statements, this is a Finding.

The results of these queries will just give an indication of what objects were created since the database installation or its most recent upgrade. It should not be used as a complete result. For example, using the queries listed above, application objects created with the database installation will not be reported as well as objects owned by default application owner accounts.

If the creation dates are more recent than the installation or latest upgrade of the application, the application may be using DDL statements.

If the listed objects are not documented in the System Security Plan, this is a Finding.

Fix Text (F-22643r1_fix)
Coordinate with the application designer to modify the application to use static objects with temporary data rather than creating and using temporary objects. Document in the System Security Plan all known object creation that supports dynamic object usage.